﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.Common;
using System.Data;

using ProgramaPuentePudahuel.App.BO;
using ProgramaPuentePudahuel.App.BLL;



/// <summary>
/// Summary description for BeneficiarioBD
/// </summary>
namespace ProgramaPuentePudahuel.App.DAL
{
    public class BeneficiarioDB
    {


        public static BeneficiarioList GetList(string id)
        {
            BeneficiarioList tempList = null;
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                SqlCommand myCommand = new SqlCommand("getBeneficiario", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@rut", id);
                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.HasRows)
                    {
                        tempList = new BeneficiarioList();
                        while (myReader.Read())
                        {
                            tempList.Add(FillDataRecord(myReader));
                        }
                    }
                    myReader.Close();
                }
            }
            
            return tempList;
        }


        public static Beneficiario GetItem(String id)
        {
            Beneficiario myBeneficiario = null;

            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                SqlCommand myCommand = new SqlCommand("getBeneficiario", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@rut",id);

                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.Read())
                    {
                        myBeneficiario = FillDataRecord(myReader);
                    }
                    myReader.Close();
                }
                myConnection.Close();
            }
            return myBeneficiario;
        }

        public static int Add(Beneficiario myBeneficiario)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                SqlCommand myCommand = new SqlCommand("ingresarBeneficiario", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;



                myCommand.Parameters.AddWithValue("@rut", myBeneficiario.rut2);
                myCommand.Parameters.AddWithValue("@estado", myBeneficiario.Estado);
                myCommand.Parameters.AddWithValue("@nombres", myBeneficiario.nombres2);
                myCommand.Parameters.AddWithValue("@apellidos", myBeneficiario.apellidos2);
                myCommand.Parameters.AddWithValue("@id_gp", myBeneficiario.id_gf2);
                
                myCommand.Parameters.AddWithValue("@fingreso", myBeneficiario.fechaIngreso2);
                myCommand.Parameters.AddWithValue("@direccion", myBeneficiario.direccion2);
                myCommand.Parameters.AddWithValue("@i_gp", myBeneficiario.i_gf2);
                myCommand.Parameters.AddWithValue("@i_ad", myBeneficiario.i_ad2);
                myCommand.Parameters.AddWithValue("@i_inf", myBeneficiario.i_inf2);

                DbParameter returnValue;
                returnValue = myCommand.CreateParameter();
                returnValue.Direction = ParameterDirection.ReturnValue;
                myCommand.Parameters.Add(returnValue);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                result = Convert.ToInt32(returnValue.Value);
                myConnection.Close();
            }
            return result;
        }


        public static int Save(Beneficiario myBeneficiario)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                SqlCommand myCommand = new SqlCommand("strprcEditBeneficiario", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                

                myCommand.Parameters.AddWithValue("@rut", myBeneficiario.rut2);
                myCommand.Parameters.AddWithValue("@estado", myBeneficiario.Estado);
                myCommand.Parameters.AddWithValue("@nombres", myBeneficiario.nombres2);
                myCommand.Parameters.AddWithValue("@apellidos", myBeneficiario.apellidos2);
                myCommand.Parameters.AddWithValue("@id_gp", myBeneficiario.id_gf2);
                myCommand.Parameters.AddWithValue("@fingreso", myBeneficiario.fechaIngreso2);
                myCommand.Parameters.AddWithValue("@direccion", myBeneficiario.direccion2);
                myCommand.Parameters.AddWithValue("@i_gp", myBeneficiario.i_gf2);
                myCommand.Parameters.AddWithValue("@i_ad", myBeneficiario.i_ad2);
                myCommand.Parameters.AddWithValue("@i_inf", myBeneficiario.i_inf2);

                DbParameter returnValue;
                returnValue = myCommand.CreateParameter();
                returnValue.Direction = ParameterDirection.ReturnValue;
                myCommand.Parameters.Add(returnValue);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                result = Convert.ToInt32(returnValue.Value);
                myConnection.Close();
            }
            return result;
        }

        public static bool Delete(string id)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                SqlCommand myCommand = new SqlCommand("strprcDelBeneficiario", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@rut", id);
                myConnection.Open();
                result = myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
            return result > 0;
        }


        #region Metodos Privados
        private static Beneficiario FillDataRecord(IDataRecord myDataRecord)
        {
            Beneficiario myBeneficiario = new Beneficiario();
            myBeneficiario.rut2 = myDataRecord.GetString(myDataRecord.GetOrdinal("RUT_BENEFICIARIO"));
            myBeneficiario.Estado = myDataRecord.GetString(myDataRecord.GetOrdinal("NOMBRE_ESTADO"));
            myBeneficiario.nombres2 = myDataRecord.GetString(myDataRecord.GetOrdinal("NOMBRES_BENEFICIARIO"));
            myBeneficiario.apellidos2 = myDataRecord.GetString(myDataRecord.GetOrdinal("APELLIDOS_BENEFICIARIO"));
            myBeneficiario.id_gf2 = myDataRecord.GetInt32(myDataRecord.GetOrdinal("ID_GF__BENEFICIARIO"));
            myBeneficiario.i_ad2 = myDataRecord.GetInt32(myDataRecord.GetOrdinal("INTEGRANTES_AD_BENEFICIARIO"));
            myBeneficiario.i_inf2 = myDataRecord.GetInt32(myDataRecord.GetOrdinal("INTEGRANTES_INF_BENEFICIARIO"));
            myBeneficiario.i_gf2 = myDataRecord.GetInt32(myDataRecord.GetOrdinal("INTEGRANTES_GF_BENEFICIARIO"));
            myBeneficiario.direccion2 = myDataRecord.GetString(myDataRecord.GetOrdinal("DIRECCION_BENEFICIARIO"));
            myBeneficiario.fechaIngreso2 = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("FECHA_INGRESO_BENEFICIARIO")); ;


          
            return myBeneficiario;
        }

        #endregion

    }
}